package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 1.编写sql语句
 * 2.调用JDBC得到结果集
 * 3.解析结果集
 * 4.返回解析的结果
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select id from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
//        因为所有增删查改操作都会连接数据库，所以，将连接数据库提取到工具类里面JDBC
//        JDBC jdbc = new JDBC();
        UserModel model = null;
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;

    }

    /**
     * 只有 id有值，怎么修改性别
     *
     * @param userModel
     * @return
     */
    @Override
    public int updateSex(UserModel userModel) {
        String sql = " update user set sex= " +
                " case  sex" +
                "    when '男' then '女' " +
                "    when '女' then '男' " +
                " end " +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int updateEnable(UserModel userModel) {
//        String sql = " update user set enable= " +
//                " case  enable" +
//                "    when 1 then 0 " +
//                "    when 0 then 1 " +
//                " end " +
//                " where id=" + userModel.getId();
//        UPDATE  表名  SET STATUS = ABS(STATUS -1) WHERE id = '1'
//        绝对值,异或
        String sql = "update user set enable=abs(enable-1) where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        int page = (userModel.getPage() - 1) * userModel.getLimit();
        int limit = userModel.getLimit();
        String sql = "select address,id,username,phone,sex,hobby,date_format(birthday,'%Y-%m-%d') as birthday,enable,email  from user ";
        sql += setSQL(userModel);
        sql += " limit " + page + "," + limit;
        ResultSet resultSet = JDBC.query(sql);
        return setUserModelList(resultSet);
    }

    /**
     * 将sql语句拼接放在一个方法里面
     *
     * @param userModel
     * @return
     */
    private String setSQL(UserModel userModel) {

        StringBuilder sql = new StringBuilder(" where  1=1 ");//为什么要加1=1；就是因为后面可能一个if语句都不执行
//         where   limit 0,10；1=1可以换成true
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and username like '%").append(userModel.getUsername()).append("%' ");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%' ");
        }
        if (!"".equals(userModel.getSex())) {
            sql.append(" and sex ='").append(userModel.getSex()).append("' ");
        }

//        对于生日，我们需要对其进行判断区间
//        对于数字也应该判断是否在某个区间
        if ("".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday <='").append(userModel.getEndBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && "".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday >='").append(userModel.getEndBirthday()).append("' ");
        }
        if (!"".equals(userModel.getStartBirthday()) && !"".equals(userModel.getEndBirthday())) {
            sql.append(" and birthday between '").append(userModel.getStartBirthday()).append("' and '").append(userModel.getEndBirthday()).append("' ");
        }
        if (userModel.getEnable() != -1) {
            sql.append(" and enable=").append(userModel.getEnable());
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql.append(" and  find_in_set('").append(hobbys[i]).append("',hobby)");
            }
        }
//        String aa=new String("aa");创建了几个对象？
//        String和StringBuilder和StringBuffer的区别？
        return sql.toString();
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) as count from user";
        sql += setSQL(userModel);
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
//            断言,会默认返回异常
            assert resultSet != null;
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select address,id,username,phone,sex,hobby,date_format(birthday,'%Y-%m-%d') as birthday,enable,email  from user where id=" + userModel.getId();
        ResultSet query = JDBC.query(sql);
        List<UserModel> list = setUserModelList(query);
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    private List<UserModel> setUserModelList(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
//                resultSet.getString("username")引号里面的对应数据库的列名
                model.setUsername(resultSet.getString("username"));
                model.setId(resultSet.getInt("id"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEmail(resultSet.getString("email"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
//        String sql="delete from user where id=?";

        return JDBC.update(sql);
    }

    @Override
    public int add(UserModel userModel) {
//        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`,\n" +
//                " `email`, `enable`, `password`,\n" +
//                " `phone`, `sex`, `update_time`, \n" +
//                " `hobby`, `birthday`, `remark`) VALUES (\n" +
//                " '" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now(),\n" +
//                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "', \n" +
//                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', sysdate(), \n" +
//                " '" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');";
        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`,\n" +
                " `email`, `enable`, `password`,\n" +
                " `phone`, `sex`, `update_time`, \n" +
                " `hobby`, `birthday`, `remark`) VALUES (?,?,now()," +
                "?,?,?," +
                "?,?,now()," +
                "?,?,'')";
        List<Object> list = new ArrayList<>();
        list.add(userModel.getUsername());
        list.add(userModel.getAddress());
        list.add(userModel.getEmail());
        list.add(userModel.getEnable());
        list.add(userModel.getPassword());
        list.add(userModel.getPhone());
        list.add(userModel.getSex());
        list.add(userModel.getHobby());
        list.add(userModel.getBirthday());

        return JDBC.updatea(sql,list);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET \n" +
                "`username` = '" + userModel.getUsername() + "',\n" +
                " `address` = '" + userModel.getAddress() + "', \n" +
                " `email` = '" + userModel.getEmail() + "', \n" +
                " `enable` = '" + userModel.getEnable() + "',\n" +
                " `phone` = '" + userModel.getPhone() + "',\n" +
                " `sex` = '" + userModel.getSex() + "', \n" +
                " `update_time` =now(), \n" +
                " `hobby` = '" + (userModel.getHobby() == null ? "" : userModel.getHobby()) + "',\n" +
                " `birthday` = '" + userModel.getBirthday() + "',\n" +
                " `remark` = '' WHERE\n" +
                " (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }


}
